[MySQL] API 구현을 위한 SQL 쿼리 작성 연습

JaehyoJJAng2025년 12월 11일

들어가며

최근 "월간 서버 입고 현황과 비용을 한눈에 파악할 수 있는 웹 대시보드를 제작하라"는 과제를 부여받았습니다.

처음 이 요구사항을 받았을 때 솔직한 생각은 이랬습니다.

"이건 아무리봐도 개발 영역 아닙니까?"

저는 개발자가 아닌 SE입니다.

그럼에도 불구하고, 요즘 같은 시대에 "개발자가 아니라서 못합니다" 라는 말은 통하지 않겠죠?

물론 AI한테 프롬프트 "띡" 던져서 그 내용대로 계획하고 구현하고 수정해나가면 못할 것은 없겠죠.

다만, 단순히 결과만 만드는 것이 아니라 SE로서의 역량을 키우는 방향으로 접근해보고 싶었습니다.

이번 과제 역시

"개발 과제가 아니라, 데이터를 이해하고 구조화하는 SE의 역할이라고 생각하며 차근차근 정리해보려고 합니다"

고민하고 있는 요구사항

현재 정리한 요구사항은 다음과 같습니다.

월별 서버 입고 현황을 확인할 수 있어야 한다.
제조사별로 얼마나 구매했는지 파악하고 싶다.
지난달 대비 이번 달 입고 수량 증감률을 확인하고 싶다.

등등 이러한 요구사항을 만족시키기 위해,

우선 SQL 쿼리를 직접 작성하며 데이터 구조를 이해하는 연습부터 해보려고 합니다.

데이터를 어떻게 집계하고, 어떤 기준으로 가공해야 하는지 명확히 해두면

추후 API 구현이나 대시보드 연동도 훨씬 쉬워지겠죠?

실습 환경 구축

1. 데이터베이스 생성

먼저 실습용으로 진행할 데이터베이스를 생성해주도록 하겠습니다.

CREATE DATABASE IF NOT EXISTS sql_practice;
USE sql_practice;

2. 테이블 생성

제가 만들 시스템은 "월간 서버 입고 관리"입니다.

이와 관련한 테이블 스키마를 생성해주도록 하겠습니다.

-- 서버 스펙 테이블 (메인임)
CREATE TABLE monthly_server_spec (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    month_key CHAR(6) NOT NULL COMMENT '입고 월 (YYYYMM)',
    domain VARCHAR(255) NOT NULL,
    hostname VARCHAR(255) NOT NULL,
    section VARCHAR(255),
    sub_section VARCHAR(255),
    os_version VARCHAR(255),
    nic_model MEDIUMTEXT,
    hdd_model MEDIUMTEXT,
    ram_model MEDIUMTEXT,
    cpu_model MEDIUMTEXT,
    mainboard_model MEDIUMTEXT,
    procurement_type VARCHAR(20) COMMENT '신규/증설/중고',
    jira_comment LONGTEXT,
    hdd_setting_date CHAR(8),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_month_key (month_key),
    INDEX idx_hostname (hostname),
    UNIQUE KEY uq_month_host (month_key, hostname)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

-- 세팅 계획 테이블
CREATE TABLE setting_plan (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    domain VARCHAR(255) NOT NULL,
    setting_date DATE NOT NULL,
    jira_comment LONGTEXT,
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_domain (domain),
    INDEX idx_setting_date (setting_date)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

-- 서버 가격 정보 테이블
CREATE TABLE server_price (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    component_type VARCHAR(50) NOT NULL,
    model_name VARCHAR(255) NOT NULL,
    unit_price INT NOT NULL,
    vendor VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_component_type (component_type),
    INDEX idx_model_name (model_name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

이제 각 테이블에 실습용 테스트 데이터를 INSERT 해주도록 하겠습니다.

-- 서버 가격 정보
INSERT INTO server_price (component_type, model_name, unit_price, vendor) VALUES
('mainboard', 'HPE ProLiant DL20 Gen10', 1800000, 'HPE'),
('mainboard', 'HPE ProLiant DL380 Gen10', 3500000, 'HPE'),
('mainboard', 'Dell PowerEdge R640', 3200000, 'Dell'),
('mainboard', 'Supermicro SYS-1029P', 2800000, 'Supermicro'),
('cpu', 'Intel Xeon E-2136', 450000, 'Intel'),
('cpu', 'Intel Xeon Gold 5218', 1800000, 'Intel'),
('cpu', 'AMD EPYC 7543', 2800000, 'AMD'),
('ram', '16GB DDR4 2666MHz', 120000, 'Samsung'),
('ram', '32GB DDR4 2933MHz', 250000, 'Samsung'),
('hdd', 'SSD 1TB', 180000, 'WD'),
('hdd', 'NVMe 2TB', 550000, 'Samsung');

-- 월간 서버 데이터 (24년 10월 ~ 25년 12월)
INSERT INTO monthly_server_spec (
    month_key, domain, hostname, section, sub_section,
    os_version, nic_model, hdd_model, ram_model, cpu_model,
    mainboard_model, procurement_type, hdd_setting_date
) VALUES
-- 2024년 10월
('202410', 'web-server-001.cafe24.com', 'web-server-001', 'cafe24', 'hosting',
 'Rocky Linux 9.3', 'Intel X550-t2 10G', 'SSD 1TB*2', '16GB DDR4 2666MHz*8', 'Intel Xeon E-2136',
 'HPE ProLiant DL20 Gen10', '신규', '20241015'),
('202410', 'web-server-002.cafe24.com', 'web-server-002', 'cafe24', 'hosting',
 'Rocky Linux 9.3', 'Intel X550-t2 10G', 'SSD 1TB*2', '16GB DDR4 2666MHz*8', 'Intel Xeon E-2136',
 'HPE ProLiant DL20 Gen10', '신규', '20241015'),
('202410', 'db-server-001.cafe24.com', 'db-server-001', 'cafe24', 'next-infra',
 'Rocky Linux 9.3', 'Intel X710 10G', 'NVMe 2TB*4', '32GB DDR4 2933MHz*8', 'Intel Xeon Gold 5218',
 'HPE ProLiant DL380 Gen10', '신규', '20241020'),

-- 2024년 11월
('202411', 'web-server-003.cafe24.com', 'web-server-003', 'cafe24', 'hosting',
 'Rocky Linux 9.3', 'Intel X550-t2 10G', 'SSD 1TB*2', '16GB DDR4 2666MHz*8', 'Intel Xeon E-2136',
 'HPE ProLiant DL20 Gen10', '증설', '20241105'),
('202411', 'k8s-master-001.devops.com', 'k8s-master-001', 'devops', 'cicd',
 'Ubuntu 22.04', 'Intel X710 10G', 'NVMe 1TB*2', '32GB DDR4 2933MHz*8', 'Intel Xeon Gold 5218',
 'Dell PowerEdge R640', '신규', '20241110'),
('202411', 'k8s-worker-001.devops.com', 'k8s-worker-001', 'devops', 'cicd',
 'Ubuntu 22.04', 'Intel X710 10G', 'NVMe 1TB*2', '32GB DDR4 2933MHz*8', 'Intel Xeon Gold 5218',
 'Dell PowerEdge R640', '신규', '20241110'),

-- 2024년 12월
('202412', 'ml-gpu-001.data.com', 'ml-gpu-001', 'data', 'analytics',
 'Ubuntu 22.04', 'Mellanox ConnectX-5 25G', 'NVMe 2TB*4', '32GB DDR4 2933MHz*16', 'AMD EPYC 7543',
 'Supermicro SYS-1029P', '신규', '20241205'),
('202412', 'ml-gpu-002.data.com', 'ml-gpu-002', 'data', 'analytics',
 'Ubuntu 22.04', 'Mellanox ConnectX-5 25G', 'NVMe 2TB*4', '32GB DDR4 2933MHz*16', 'AMD EPYC 7543',
 'Supermicro SYS-1029P', '신규', '20241205'),
('202412', 'web-server-004.cafe24.com', 'web-server-004', 'cafe24', 'hosting',
 'Rocky Linux 9.3', 'Intel X550-t2 10G', 'SSD 1TB*2', '16GB DDR4 2666MHz*8', 'Intel Xeon E-2136',
 'HPE ProLiant DL20 Gen10', '교체', '20241210'),

-- 2025년 1월 ~ 12월
('202501', 'web-server-005.cafe24.com', 'web-server-005', 'cafe24', 'hosting',
 'Rocky Linux 9.3', 'Intel X550-t2 10G', 'SSD 1TB*2', '16GB DDR4 2666MHz*8', 'Intel Xeon E-2136',
 'HPE ProLiant DL20 Gen10', '증설', '20250110'),
('202512', 'ec-cloud-001.cafe24.com', 'ec-cloud-001', 'cafe24', 'ec-cloud',
 'Rocky Linux 9.3', 'Intel X550-t2 10G', 'SSD 1TB*2', '16GB DDR4 2666MHz*8', 'Intel Xeon E-2136',
 'HPE ProLiant DL20 Gen10', '신규', '20251204'),
('202512', 'ec-cloud-002.cafe24.com', 'ec-cloud-002', 'cafe24', 'ec-cloud',
 'Rocky Linux 9.3', 'Intel X550-t2 10G', 'SSD 1TB*2', '16GB DDR4 2666MHz*8', 'Intel Xeon E-2136',
 'HPE ProLiant DL20 Gen10', '신규', '20251204');

-- 세팅 계획
INSERT INTO setting_plan (domain, setting_date, jira_comment, status) VALUES
('web-server-001.cafe24.com', '2024-10-15', 'Q4 웹서버 증설', 'completed'),
('ec-cloud-001.cafe24.com', '2025-12-04', 'EC Cloud 확장', 'completed');

INSERT한 데이터가 실제 테이블에 맞게 들어갔는지 확인해봅시다.

SELECT COUNT(*) AS '전체 입고 서버 수' FROM monthly_server_spec;

+--------------------------+
| 전체 입고 서버 수        |
+--------------------------+
|                       12 |
+--------------------------+
1 row in set (0.00 sec)

Level 1: SQL 기초

해당 챕터에서는 SQL의 기초 중에 기초를 다루면서 SQL문을 작성하는 감을 익혀보도록 하겠습니다.

(기본적인 SELECT 조회는 넘어가도록 하겠습니다.)

WHERE - 조건 필터링

문제 1: 특정 월 데이터만 조회

-- 2025년 12월 입고된 서버 조회
SELECT hostname, nic_model, hdd_model, procurement_type FROM monthly_server_spec WHERE month_key = '202512';

+--------------+-------------------+-----------+------------------+
| hostname     | nic_model         | hdd_model | procurement_type |
+--------------+-------------------+-----------+------------------+
| ec-cloud-001 | Intel X550-t2 10G | SSD 1TB*2 | 신규             |
| ec-cloud-002 | Intel X550-t2 10G | SSD 1TB*2 | 신규             |
+--------------+-------------------+-----------+------------------+
2 rows in set (0.00 sec)

문제 2: 조달 유형이 '신규'인 서버만 조회

SELECT
    hostname AS '호스트명',
    mainboard_model AS '메인보드',
    procurement_type AS '조달 구분'
FROM monthly_server_spec
WHERE procurement_Type = '신규';

+----------------+--------------------------+---------------+
| 호스트명       | 메인보드                 | 조달 구분     |
+----------------+--------------------------+---------------+
| web-server-001 | HPE ProLiant DL20 Gen10  | 신규          |
| web-server-002 | HPE ProLiant DL20 Gen10  | 신규          |
| db-server-001  | HPE ProLiant DL380 Gen10 | 신규          |
| k8s-master-001 | Dell PowerEdge R640      | 신규          |
| k8s-worker-001 | Dell PowerEdge R640      | 신규          |
| ml-gpu-001     | Supermicro SYS-1029P     | 신규          |
| ml-gpu-002     | Supermicro SYS-1029P     | 신규          |
| ec-cloud-001   | HPE ProLiant DL20 Gen10  | 신규          |
| ec-cloud-002   | HPE ProLiant DL20 Gen10  | 신규          |
+----------------+--------------------------+---------------+
9 rows in set (0.01 sec)

💡 팁: AS로 컬럼명을 변경할 수 있습니다.

문제 3: 여러 조건 (AND)

-- 2025년 12월 + 신규
SELECT domain, procurement_type, month_key
FROM monthly_server_spec
WHERE month_key = '202512' AND procurement_type = '신규';

+-------------------------+------------------+-----------+
| domain                  | procurement_type | month_key |
+-------------------------+------------------+-----------+
| ec-cloud-001.cafe24.com | 신규             | 202512    |
| ec-cloud-002.cafe24.com | 신규             | 202512    |
+-------------------------+------------------+-----------+
2 rows in set (0.00 sec)

문제 4: 패턴 매칭 (LIKE)

-- hostname에 'web'이 포함된 서버
SELECT
    hostname,
    procurement_type
FROM monthly_server_spec
WHERE hostname LIKE '%web%';

+----------------+------------------+
| hostname       | procurement_type |
+----------------+------------------+
| web-server-001 | 신규             |
| web-server-002 | 신규             |
| web-server-003 | 증설             |
| web-server-004 | 교체             |
| web-server-005 | 증설             |
+----------------+------------------+
5 rows in set (0.00 sec)

Level 1 종합 문제

문제: 2025년에 입고된 신규 서버 중 cafe24 섹션만, 최신순으로 10개 조회

SELECT
    hostname,
    procurement_type,
    section,
    hdd_setting_date
FROM monthly_server_spec
WHERE month_key BETWEEN '202501' AND '202512' AND section = 'cafe24' order by hdd_setting_date desc LIMIT 10;

+----------------+------------------+---------+------------------+
| hostname       | procurement_type | section | hdd_setting_date |
+----------------+------------------+---------+------------------+
| ec-cloud-001   | 신규             | cafe24  | 20251204         |
| ec-cloud-002   | 신규             | cafe24  | 20251204         |
| web-server-005 | 증설             | cafe24  | 20250110         |
+----------------+------------------+---------+------------------+
3 rows in set (0.00 sec)

Level 2: 집계 함수

~의 개수는?
~의 평균은?
~의 합계는?

COUNT - 개수 세기

문제 1: COUNT(*) vs COUNT(컬럼)

SELECT
    COUNT(*) AS '전체',
    COUNT(jira_comment) AS 'jira_있음',
    COUNT(*) - COUNT(jira_comment) AS 'jira_없음'
FROM monthly_server_spec;

COUNT(*): 모든 행 개수
COUNT(컬럼): NULL이 아닌 행 개수

MAX,MIN - 최대/최소

-- 가장 최근/오래된 입고일
SELECT 
    MAX(hdd_setting_date) AS '최근 입고일',
    MIN(hdd_setting_date) AS '최초 입고일'
FROM monthly_server_spec;

+------------------+------------------+
| 최근 입고일      | 최초 입고일      |
+------------------+------------------+
| 20251204         | 20241015         |
+------------------+------------------+
1 row in set (0.00 sec)

Level 2 종합 문제

문제: 2025년 12월 서버 통계

SELECT
    COUNT(*) AS "총 서버",
    SUM(CASE WHEN procurement_type = '신규' THEN 1 else 0 END) AS '신규',
    SUM(CASE WHEN procurement_type = '증설' THEN 1 else 0 END) AS '증설',
    SUM(CASE WHEN procurement_type = '교체' THEN 1 else 0 END) AS '교체',
    COUNT(DISTINCT mainboard_model) AS '메인보드 종류'
FROM monthly_server_spec;

+------------+--------+--------+--------+---------------------+
| 총 서버    | 신규   | 증설   | 교체   | 메인보드 종류       |
+------------+--------+--------+--------+---------------------+
|         12 |      9 |      2 |      1 |                   4 |
+------------+--------+--------+--------+---------------------+
1 row in set (0.00 sec)

Level 3: GROUPB BY

가장 중요한 파트입니다!

GROUP BY란?

"~별로" 통계를 낼 때 사용
예시
- 월별 서버 수
- 제조사별 서버 수
- 섹션별 서버 수

기본 패턴

SELECT
    그룹_컬럼,
    COUNT(*) AS 개수
FROM 테이블
GROUP BY 그룹_컬럼;

문제 1: 월별 서버 입고 개수 파악

SELECT
    month_key AS '월',
    COUNT(*) AS '서버 수'
FROM monthly_server_spec
GROUP BY month_key
ORDER BY month_key;

+--------+------------+
| 월     | 서버 수    |
+--------+------------+
| 202410 |          3 |
| 202411 |          3 |
| 202412 |          3 |
| 202501 |          1 |
| 202512 |          2 |
+--------+------------+
5 rows in set (0.00 sec)

핵심: month_key로 묶어서 각 월의 개수를 센다!

문제 2: 여러 컬럼으로 GROUP BY

-- 섹션과 서브섹션별 GROUP BY
SELECT
    section AS '섹션',
    sub_section AS '서브섹션',
    COUNT(*) AS '서버 수'
FROM monthly_server_spec
GROUP BY section, sub_section
ORDER BY section, sub_section;

+--------+--------------+------------+
| 섹션   | 서브섹션     | 서버 수    |
+--------+--------------+------------+
| cafe24 | ec-cloud     |          2 |
| cafe24 | hosting      |          5 |
| cafe24 | next-infra   |          1 |
| data   | analytics    |          2 |
| devops | cicd         |          2 |
+--------+--------------+------------+
5 rows in set (0.00 sec)

문제 3: GROUP BY + 집계 함수

-- 제조사별 통계
SELECT
    CASE
        WHEN mainboard_model LIKE '%HPE%' THEN 'HPE'
        WHEN mainboard_model LIKE '%Dell%' THEN 'Dell'
        WHEN mainboard_model LIKE '%Supermicro%' THEN 'Supermicro'
        ELSE 'Other'
    END AS '제조사',
    COUNT(*) AS '서버_수',
    MAX(hdd_setting_date) AS '최근_입고일',
    MIN(hdd_setting_date) AS '최초_입고일'
FROM monthly_server_spec
GROUP BY
    CASE
        WHEN mainboard_model LIKE '%HPE%' THEN 'HPE'
        WHEN mainboard_model LIKE '%Dell%' THEN 'Dell'
        WHEN mainboard_model LIKE '%Supermicro%' THEN 'Supermicro'
        ELSE 'Other'
    END
ORDER BY COUNT(*) DESC;

`HAVING` - GROUP BY 결과 필터링

WHERE vs HAVING

WHERE: 그룹화 전 필터링
HAVING: 그룹화 후 필터링

문제 1: 서버가 2대 이상인 월만 조회

-- 2025년 데이터 중에서 (WHERE)
-- 서버가 2대 이상인 월만 (HAVING)
SELECT
    month_key AS '월',
    COUNT(*) AS '서버_수'
FROM monthly_server_spec
WHERE month_key >= '202501'  -- ⭐ 그룹화 전
GROUP BY month_key
HAVING COUNT(*) >= 2         -- ⭐ 그룹화 후
ORDER BY month_key;

+--------+------------+
| 월     | 서버_수    |
+--------+------------+
| 202512 |          2 |
+--------+------------+
1 row in set (0.00 sec)

Tag -